create or replace
procedure createRelationship(v_movie varchar2, v_person varchar2, v_role number, v_award number, v_type number, v_calendar number) is
  CURSOR cur_movie IS SELECT * FROM movie WHERE name = v_movie;
  CURSOR cur_person IS SELECT id FROM person WHERE name = v_person;
  v_sql varchar2(1131);
  v_cnt number;
begin
  FOR movie IN cur_movie 
  LOOP
    FOR person IN cur_person
    LOOP
      v_sql := 'select count(*) from mpr where movie_id='||movie.id||' and person_id='||person.id ||' and role_id='||v_role;
      --dbms_output.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql into v_cnt;
      if v_cnt =0 then
        --dbms_output.put_line('MPR DOES NOT EXIST');
        v_sql := 'insert into mpr values (sq_mpr_id.nextval, '||movie.id||', '||person.id||', '||v_role||')';
        dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
      --else
        --dbms_output.put_line('MPR EXISTS!');
      end if;
      
      v_sql := 'insert into mpac values (sq_mpac_id.nextval, '||movie.id||', '|| v_award ||', '||movie.calendar_id ||', '|| v_type ||','||person.id||')';
      dbms_output.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql;
    END LOOP;
  END LOOP;
end createRelationship;

declare
  CURSOR cur_movie IS SELECT * FROM movie;
  v_sql varchar2(1131);
  v_cnt number;
begin
  FOR movie IN cur_movie
  LOOP
   
      v_sql := 'select count(*) from movie_category where movie_id='||movie.id;
      --dbms_output.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql into v_cnt;
      if v_cnt =0 then
        v_sql := 'insert into movie_category values ('||movie.id||', 1)';
        dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
      end if;
      
      v_sql := 'select count(*) from movie_country where movie_id='||movie.id;
      --dbms_output.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql into v_cnt;
      if v_cnt =0 then
        v_sql := 'insert into movie_country values ('||movie.id||', 1)';
        dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
      end if;
      
      v_sql := 'select count(*) from movie_language where movie_id='||movie.id;
      --dbms_output.put_line(v_sql);
      EXECUTE IMMEDIATE v_sql into v_cnt;
      if v_cnt =0 then
        v_sql := 'insert into movie_language values ('||movie.id||', 1)';
        dbms_output.put_line(v_sql);
        EXECUTE IMMEDIATE v_sql;
      end if;
      
  END LOOP;
end;
/


--------------------

delete from movie where id=10145;

delete from movie_category o where not exists (select * from movie where id=o.movie_id);
delete from movie_country o where not exists (select * from movie where id=o.movie_id);
delete from movie_image o where not exists (select * from movie where id=o.movie_id);
delete from movie_language o where not exists (select * from movie where id=o.movie_id);
delete from movie_datetime o where not exists (select * from movie where id=o.movie_id);
commit;